import Database from 'better-sqlite3'
import { fileURLToPath } from 'url'
import { dirname, join } from 'path'
import { existsSync, mkdirSync } from 'fs'

const __filename = fileURLToPath(import.meta.url)
const __dirname = dirname(__filename)

const dbPath = process.env.DB_PATH || join(__dirname, '../data/wallet.db')
const dbDir = dirname(dbPath)

// 确保数据库目录存在
if (!existsSync(dbDir)) {
  mkdirSync(dbDir, { recursive: true })
}

const db = new Database(dbPath)

// 启用外键约束
db.pragma('foreign_keys = ON')

// 初始化数据库表
export function initDatabase() {
  // 用户表
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id TEXT PRIMARY KEY,
      username TEXT UNIQUE NOT NULL,
      email TEXT UNIQUE NOT NULL,
      password_hash TEXT NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `)

  // 钱包表
  db.exec(`
    CREATE TABLE IF NOT EXISTS wallets (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL,
      currency TEXT NOT NULL,
      chain TEXT DEFAULT 'ETH',
      address TEXT NOT NULL,
      private_key_encrypted TEXT NOT NULL,
      balance REAL DEFAULT 0,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
      UNIQUE(user_id, currency, chain)
    )
  `)
  
  // 迁移：为现有钱包添加 chain 字段（如果不存在）
  try {
    db.exec(`ALTER TABLE wallets ADD COLUMN chain TEXT DEFAULT 'ETH'`)
    console.log('已添加 chain 字段到 wallets 表')
  } catch (e) {
    // 字段已存在，忽略错误
  }

  // 交易表
  db.exec(`
    CREATE TABLE IF NOT EXISTS transactions (
      id TEXT PRIMARY KEY,
      tx_id TEXT UNIQUE,
      user_id TEXT NOT NULL,
      type TEXT NOT NULL,
      currency TEXT NOT NULL,
      chain TEXT DEFAULT 'ETH',
      amount REAL NOT NULL,
      from_address TEXT,
      to_address TEXT NOT NULL,
      status TEXT DEFAULT 'pending',
      memo TEXT,
      block_number INTEGER,
      confirmations INTEGER DEFAULT 0,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )
  `)
  
  // 迁移：为现有交易添加 chain 字段（如果不存在）
  try {
    db.exec(`ALTER TABLE transactions ADD COLUMN chain TEXT DEFAULT 'ETH'`)
    console.log('已添加 chain 字段到 transactions 表')
  } catch (e) {
    // 字段已存在，忽略错误
  }

  // 充值地址表（用于追踪待确认的充值）
  db.exec(`
    CREATE TABLE IF NOT EXISTS deposit_addresses (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL,
      currency TEXT NOT NULL,
      address TEXT NOT NULL,
      status TEXT DEFAULT 'active',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )
  `)

  // C2C订单表
  db.exec(`
    CREATE TABLE IF NOT EXISTS c2c_orders (
      id TEXT PRIMARY KEY,
      order_id TEXT UNIQUE NOT NULL,
      user_id TEXT NOT NULL,
      type TEXT NOT NULL CHECK(type IN ('buy', 'sell')),
      currency TEXT NOT NULL DEFAULT 'USDT',
      amount REAL NOT NULL,
      price REAL NOT NULL,
      min_amount REAL DEFAULT 0,
      max_amount REAL,
      payment_method TEXT,
      status TEXT DEFAULT 'active' CHECK(status IN ('active', 'completed', 'cancelled', 'expired')),
      remark TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      expires_at DATETIME,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )
  `)

  // C2C交易表
  db.exec(`
    CREATE TABLE IF NOT EXISTS c2c_trades (
      id TEXT PRIMARY KEY,
      trade_id TEXT UNIQUE NOT NULL,
      order_id TEXT NOT NULL,
      buyer_id TEXT NOT NULL,
      seller_id TEXT NOT NULL,
      currency TEXT NOT NULL DEFAULT 'USDT',
      amount REAL NOT NULL,
      price REAL NOT NULL,
      total_amount REAL NOT NULL,
      status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'paid', 'completed', 'cancelled', 'disputed')),
      payment_method TEXT,
      payment_info TEXT,
      buyer_remark TEXT,
      seller_remark TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      paid_at DATETIME,
      completed_at DATETIME,
      FOREIGN KEY (order_id) REFERENCES c2c_orders(id) ON DELETE CASCADE,
      FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE CASCADE,
      FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE
    )
  `)

  // 聊天消息表（低耦合设计，通过context_type和context_id关联业务）
  db.exec(`
    CREATE TABLE IF NOT EXISTS chat_messages (
      id TEXT PRIMARY KEY,
      message_id TEXT UNIQUE NOT NULL,
      context_type TEXT NOT NULL DEFAULT 'c2c_trade',
      context_id TEXT NOT NULL,
      sender_id TEXT NOT NULL,
      receiver_id TEXT NOT NULL,
      content TEXT NOT NULL,
      message_type TEXT DEFAULT 'text' CHECK(message_type IN ('text', 'image', 'file', 'system')),
      is_read INTEGER DEFAULT 0,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
      FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE
    )
  `)

  // 实名认证表
  db.exec(`
    CREATE TABLE IF NOT EXISTS identity_verifications (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL UNIQUE,
      real_name TEXT NOT NULL,
      id_card_number TEXT NOT NULL,
      id_card_front_url TEXT,
      id_card_back_url TEXT,
      face_image_url TEXT,
      verification_status TEXT DEFAULT 'pending' CHECK(verification_status IN ('pending', 'processing', 'approved', 'rejected')),
      rejection_reason TEXT,
      tencent_request_id TEXT,
      tencent_result TEXT,
      face_compare_score REAL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      verified_at DATETIME,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )
  `)

  // 创建索引
  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_wallets_user_id ON wallets(user_id);
    CREATE INDEX IF NOT EXISTS idx_wallets_currency ON wallets(currency);
    CREATE INDEX IF NOT EXISTS idx_wallets_chain ON wallets(chain);
    CREATE INDEX IF NOT EXISTS idx_transactions_user_id ON transactions(user_id);
    CREATE INDEX IF NOT EXISTS idx_transactions_tx_id ON transactions(tx_id);
    CREATE INDEX IF NOT EXISTS idx_transactions_status ON transactions(status);
    CREATE INDEX IF NOT EXISTS idx_transactions_chain ON transactions(chain);
    CREATE INDEX IF NOT EXISTS idx_deposit_addresses_address ON deposit_addresses(address);
    CREATE INDEX IF NOT EXISTS idx_c2c_orders_user_id ON c2c_orders(user_id);
    CREATE INDEX IF NOT EXISTS idx_c2c_orders_type ON c2c_orders(type);
    CREATE INDEX IF NOT EXISTS idx_c2c_orders_status ON c2c_orders(status);
    CREATE INDEX IF NOT EXISTS idx_c2c_orders_currency ON c2c_orders(currency);
    CREATE INDEX IF NOT EXISTS idx_c2c_trades_order_id ON c2c_trades(order_id);
    CREATE INDEX IF NOT EXISTS idx_c2c_trades_buyer_id ON c2c_trades(buyer_id);
    CREATE INDEX IF NOT EXISTS idx_c2c_trades_seller_id ON c2c_trades(seller_id);
    CREATE INDEX IF NOT EXISTS idx_c2c_trades_status ON c2c_trades(status);
    CREATE INDEX IF NOT EXISTS idx_chat_messages_context ON chat_messages(context_type, context_id);
    CREATE INDEX IF NOT EXISTS idx_chat_messages_sender ON chat_messages(sender_id);
    CREATE INDEX IF NOT EXISTS idx_chat_messages_receiver ON chat_messages(receiver_id);
    CREATE INDEX IF NOT EXISTS idx_chat_messages_created_at ON chat_messages(created_at);
    CREATE INDEX IF NOT EXISTS idx_identity_verifications_user_id ON identity_verifications(user_id);
    CREATE INDEX IF NOT EXISTS idx_identity_verifications_status ON identity_verifications(verification_status);
  `)

  console.log('数据库初始化完成')
}

// 生成唯一ID
export function generateId() {
  return `${Date.now()}-${Math.random().toString(36).substr(2, 9)}`
}

export default db

